package com.hnust.dao;

import com.hnust.domain.Major;
import com.hnust.domain.Student;
import com.hnust.domain.record.SearchStudentInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 *创建人：张汝往
 *创建时间：2020/10/6
 *描述：
 */
@Mapper
@Repository
public interface StudentDao {

    //根据班级ID查找学生总数
    @Select("select count(*) from student where cls_id=#{classId} order by stu_number")
    @ResultType(Integer.class)
    public Integer findStudentCountByClassId(String classId);


    //根据学院ID 学生搜索类（里面的属性可能为空）查询所有学生
    @SelectProvider(type = findStudentBySearchProvider.class,method = "findStudentBySearchSql")
    @ResultType(Student.class)
    public List<Student> findStudentBySearch(String collegeId, SearchStudentInfo searchStudentInfo,Integer limitN,Integer limitM);
    class findStudentBySearchProvider{
        public String findStudentBySearchSql(String collegeId, SearchStudentInfo searchStudentInfo,Integer limitN,Integer limitM){

            String sql="select student.* from student,class where class.cla_id=student.cls_id ";
            if(searchStudentInfo!=null){
                if(searchStudentInfo.getGrade()!=null&&!searchStudentInfo.getGrade().equals("")){
                    sql=sql+" and  class.grade=#{searchStudentInfo.grade}";
                }
//                if(searchStudentInfo.getAccount()!=null&&!searchStudentInfo.getAccount().equals("")){
//                    sql=sql+" and student.account=#{searchStudentInfo.account}";
//                }
                if(searchStudentInfo.getClassNumber()!=null&&!searchStudentInfo.getClassNumber().equals("")){
                    sql=sql+" and class.cla_number=#{searchStudentInfo.classNumber}";
                }
                if(searchStudentInfo.getMajor()!=null&&!searchStudentInfo.getMajor().equals("")){
                    sql=sql+" and class.maj_id=#{searchStudentInfo.major}";
                }
                if(searchStudentInfo.getName()!=null&&!searchStudentInfo.getName().equals("")){
                    sql=sql+" and student.name like concat('%',#{searchStudentInfo.name},'%') ";
                }
                if(searchStudentInfo.getStuNumber()!=null&&!searchStudentInfo.getStuNumber().equals("")){
                    sql=sql+" and student.stu_number like concat('%',#{searchStudentInfo.stuNumber},'%')" ;
                }
            }
            sql=sql+" and class.col_id=#{collegeId} order by student.stu_number limit #{limitM},#{limitN}";
            return sql;
        }
    }

    //根据学院ID 学生搜索类（里面的属性可能为空）查询学生总数
    @SelectProvider(type = findStudentCountBySearchProvider.class,method = "findStudentCountBySearchSql")
    @ResultType(Integer.class)
    public Integer findStudentCountBySearch(String collegeId, SearchStudentInfo searchStudentInfo);
    class findStudentCountBySearchProvider{
        public String findStudentCountBySearchSql(String collegeId, SearchStudentInfo searchStudentInfo){
            String sql="select count(*) from student,class where class.cla_id=student.cls_id ";
            if(searchStudentInfo!=null){
                if(searchStudentInfo.getGrade()!=null&&!searchStudentInfo.getGrade().equals("")){
                    sql=sql+" and  class.grade=#{searchStudentInfo.grade}";
                }
//                if(searchStudentInfo.getAccount()!=null&&!searchStudentInfo.getAccount().equals("")){
//                    sql=sql+" and student.account=#{searchStudentInfo.account}";
//                }
                if(searchStudentInfo.getClassNumber()!=null&&!searchStudentInfo.getClassNumber().equals("")){
                    sql=sql+" and class.cla_number=#{searchStudentInfo.classNumber}";
                }
                if(searchStudentInfo.getMajor()!=null&&!searchStudentInfo.getMajor().equals("")){
                    sql=sql+" and class.maj_id=#{searchStudentInfo.major}";
                }
                if(searchStudentInfo.getName()!=null&&!searchStudentInfo.getName().equals("")){
                    sql=sql+" and student.name like concat('%',#{searchStudentInfo.name},'%')";
                }
                if(searchStudentInfo.getStuNumber()!=null&&!searchStudentInfo.getStuNumber().equals("")){
                    sql=sql+" and student.stu_number like concat('%',#{searchStudentInfo.stuNumber},'%')";
                }
            }
            sql=sql+"and class.col_id=#{collegeId}";
            return sql;
        }
    }

    //根据学生ID删除学生
    @Delete("delete from student where stu_id=#{id} order by stu_number")
    @ResultType(Boolean.class)
    public Boolean deleteStudentById(String id);

    //根据学生ID更新密码
    @Update("update student set password=#{password} where stu_id=#{stuId}")
    @ResultType(Boolean.class)
    public Boolean updataStudentPassword(String password,String stuId);

    //根据学生ID查找学生账号
    @Select("select account from student where stu_id=#{stuId} order by stu_number")
    @ResultType(String.class)
    public String getAccountById(String stuId);

    //根据学生ID查找学生
    @Select("select * from student where stu_id=#{stuId} order by stu_number")
    @ResultType(Student.class)
    public Student getStudentById(String stuId);

    /*
     * @Author Tonited
     * @Description //TODO 插入一个新学生
     * @Date 9:47 2020/10/30
     * @Param [stu_id, account, alias, name, stu_number, pho_number, e_mail, password, school, cls_id]
     * @return java.lang.Integer
     **/
    @Insert("insert into student (stu_id, account, alias, name, stu_number, pho_number, e_mail, password, school, cls_id) value (#{stu_id}, #{account}, #{alias}, #{name}, #{stu_number}, #{pho_number}, #{e_mail}, #{password}, #{school}, #{cls_id})")
    @ResultType(Integer.class)
    Integer insertStudent(String stu_id, String account, String alias, String name, String stu_number, String pho_number, String e_mail, String password, String school, String cls_id);

}
